package com.dzm.Test05.dao.impl;

import com.dzm.Test05.dao.DeptDao;
import com.dzm.Test05.entity.Dept;
import com.dzm.Test05.entity.Emp;
import com.dzm.Test05.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 作者：Dzm
 * 时间：2024-07-26-20:53
 * 功能：
 */
public class DeptDaoImpl implements DeptDao {
    @Override
    public List<Dept> getAllDepts() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Dept> deptList = new ArrayList<>();

        //sql语句
        String sql = "select * from dept";
        try {
            conn = DBUtil.getConnect();
            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            while (rs != null && rs.next()) {
                String did = rs.getString("did");
                String dname = rs.getString("dname");
                String dcity = rs.getString("dcity");

                Dept dept = new Dept(did, dname, dcity);
                deptList.add(dept);
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return deptList;
    }

    @Override
    public List<Emp> getAllEmpByDept(String id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Emp> empList = new ArrayList<>();

        //sql语句
        String sql = "SELECT e.*, w.salary " +
                "FROM dept d LEFT JOIN `work` w ON d.did = w.did " +
                "LEFT JOIN emp e ON w.eid = e.eid " +
                "WHERE d.did = ?;";
        try {
            conn = DBUtil.getConnect();
            ps = conn.prepareStatement(sql);

            ps.setObject(1, id);

            rs = ps.executeQuery();

            while (rs != null && rs.next()) {
                String eid = rs.getString("eid");
                String ename = rs.getString("ename");
                Date bdate = rs.getDate("bdate");
                String sex = rs.getString("sex");
                String city = rs.getString("city");
                String isdel = rs.getString("isdel");
                Double salary = rs.getDouble("salary");

                Emp emp = new Emp(eid, ename, bdate, sex, city, isdel, salary);
                empList.add(emp);
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return empList;
    }
}
